﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using PMQLCHH.DTO;

namespace PMQLCHH.DAO
{
    class MatHangDAO
    {
        //Kha Nguyen - Mat hang DAO - 6/5/2013 - begin 
        /// <summary>
        /// Them Mat Hang
        /// </summary>
        /// 
        public bool ThemMatHang(MatHangDTO MH)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("INSERT INTO MATHANG(MaMatHang,TenMatHang,LoaiMatHang,DonViTinh,SoLuongTon,GiaThamKhao,GhiChu,TrangThai) VALUES ('{0}',N'{1}',{2},{3},{4},{5},N'{6}',{7})", MH.MaMatHang, MH.TenMatHang, MH.LoaiMatHang, MH.DonViTinh, MH.SoLuongTon, MH.GiaThamKhao, MH.Ghichu_MH, MH.TrangThai);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        /// <summary>
        /// Kiem tra ma Mat Hang
        /// </summary>
        /// 
        public bool KiemTraMaMatHang(string MaMH)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("SELECT * FROM MATHANG WHERE MaMatHang = '{0}'", MaMH);
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            if (dt.Rows.Count != 0)
            {
                KQ = true;
            }
            return KQ;
        }

        /// <summary>
        /// Kiem tra ma Mat Hang da bat
        /// </summary>
        /// 
        public bool MaMatHangDaBat(string MaMH)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("SELECT * FROM MATHANG WHERE MaMatHang = '{0}'", MaMH);
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            if (dt.Rows.Count != 0)
            {
                if (int.Parse(dt.Rows[0]["TrangThai"].ToString()) == 1)
                {
                    KQ = true;
                }
            }
            return KQ;
        }

        /// <summary>
        /// lay chi tiet Mat Hang
        /// </summary>
        /// 
        public DataTable LayChiTietMatHang(string MaMH)
        {
            string strQuery = "";
            strQuery = String.Format("SELECT MaMatHang,TenMatHang,TenLoaiMatHang,TenDonVi,SoLuongTon,GiaThamKhao,GhiChu FROM MATHANG MH,LOAIMATHANG L, DONVITINH D WHERE MH.LoaiMatHang = L.MaLoaiMatHang AND MH.DonViTinh = D.MaDonVi AND TrangThai = 1 AND MaMatHang = '{0}'", MaMH);
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// Them Mat Hang
        /// </summary>
        /// 
        public bool NhapMatHang(MatHangDTO MH)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("INSERT INTO MATHANG(MaMatHang,TenMatHang,LoaiMatHang,DonViTinh,SoLuongTon,GiaThamKhao,GhiChu,TrangThai,NgayLap) VALUES ('{0}',N'{1}',{2},{3},{4},{5},N'{6}',{7},'{8}')", MH.MaMatHang, MH.TenMatHang, MH.LoaiMatHang, MH.DonViTinh, MH.SoLuongTon, MH.GiaThamKhao, MH.Ghichu_MH, MH.TrangThai, MH.NgayLap);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        /// <summary>
        /// Xoa 1 Mat Hang
        /// </summary>
        /// 
        public bool XoaMatHang(string MaMH)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("DELETE FROM MATHANG WHERE MaMatHang = '{0}'", MaMH);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        /// <summary>
        /// Kiem tra Mat Hang co ton tai trong chi tiet hoa don
        /// </summary>
        /// 
        public bool DaDatHoaDon(string MaMH)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("SELECT * FROM CHITIETHOADON WHERE MaMatHang = '{0}'", MaMH);
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            if (dt.Rows.Count != 0)
            {
                KQ = true;
            }
            return KQ;
        }

        /// <summary>
        /// Kiem tra Mat Hang co ton tai trong chi tiet phieu nhap
        /// </summary>
        /// 
        public bool DaDatPhieuNhap(string MaMH)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("SELECT * FROM ChiTietPhieuNhapHang WHERE MaMatHang = '{0}'", MaMH);
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            if (dt.Rows.Count != 0)
            {
                KQ = true;
            }
            return KQ;
        }

        /// <summary>
        /// Kiem tra Mat Hang co ton tai trong chi tiet phieu xuat
        /// </summary>
        /// 
        public bool DaDatPhieuXuat(string MaMH)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("SELECT * FROM ChiTietPhieuGoiHang WHERE MaMatHang = '{0}'", MaMH);
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            if (dt.Rows.Count != 0)
            {
                KQ = true;
            }
            return KQ;
        }

        /// <summary>
        /// Cap Nhat Mat Hang
        /// </summary>
        /// 
        public bool CapNhatMatHang(MatHangDTO MH)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("UPDATE MATHANG SET TenMatHang = N'{0}', LoaiMatHang = {1}, DonViTinh = {2}, GhiChu = N'{3}', TrangThai = {4} WHERE MaMatHang = '{5}'", MH.TenMatHang, MH.LoaiMatHang, MH.DonViTinh, MH.Ghichu_MH, MH.TrangThai, MH.MaMatHang);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        /// <summary>
        /// Lay ma Mat Hang moi nhat
        /// </summary>
        /// 
        public DataTable LayMaMatHangMoiNhat()
        {
            string strQuery = "";
            strQuery = "SELECT TOP 1 MaMatHang FROM MATHANG ORDER BY MaMatHang DESC";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// lay danh sach Mat Hang
        /// </summary>
        /// 
        public DataTable LayDanhSachMatHang()
        {
            string strQuery = "";
            strQuery = "SELECT MaMatHang,TenMatHang,TenLoaiMatHang,TenDonVi,SoLuongTon,GiaThamKhao,GhiChu,CASE TrangThai WHEN 1 THEN N'Bật' ELSE N'Tắt' END 'TrangThai',MH.NgayLap FROM MATHANG MH,LOAIMATHANG L, DONVITINH D WHERE MH.LoaiMatHang = L.MaLoaiMatHang AND MH.DonViTinh = D.MaDonVi ORDER BY MaMatHang DESC ";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// lay danh sach Mat Hang dang kinh danh
        /// </summary>
        /// 
        public DataTable LayDanhSachMatHangDeBan()
        {
            string strQuery = "";
            strQuery = "SELECT MaMatHang,TenMatHang,TenLoaiMatHang,TenDonVi,SoLuongTon,GiaThamKhao,GhiChu FROM MATHANG MH,LOAIMATHANG L, DONVITINH D WHERE MH.LoaiMatHang = L.MaLoaiMatHang AND MH.DonViTinh = D.MaDonVi AND TrangThai = 1 AND SoLuongTon > 0 ";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// lay du lieu mat hang
        /// </summary>
        /// 
        public DataTable LayBangMatHang()
        {
            string strQuery = "";
            strQuery = "SELECT * FROM MATHANG";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// lay danh sach loai mat hang
        /// </summary>
        /// 
        public DataTable LayDanhSachLoaiMatHang()
        {
            string strQuery = "";
            strQuery = "SELECT * FROM LOAIMATHANG";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// lay ma loai mat hang
        /// </summary>
        /// 
        public int LayMaLoaiMatHang(string TenLoai)
        {
            string strQuery = "";
            int KQ = 0;
            strQuery = String.Format("SELECT MaLoaiMatHang FROM LOAIMATHANG WHERE TenLoaiMatHang = N'{0}'", TenLoai);
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            if (dt.Rows.Count > 0)
            {
                KQ = (int)dt.Rows[0][0];
            }
            return KQ;
        }

        /// <summary>
        /// lay danh sach Don Vi Tinh
        /// </summary>
        /// 
        public DataTable LayDanhSachDonViTinh()
        {
            string strQuery = "";
            strQuery = "SELECT * FROM DONVITINH";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// lay ma Don Vi Tinh
        /// </summary>
        /// 
        public int LayMaDonViTinh(string TenDVT)
        {
            string strQuery = "";
            int KQ = 0;
            strQuery = String.Format("SELECT MaDonVi FROM DONVITINH WHERE TenDonVi = N'{0}'", TenDVT);
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            if (dt.Rows.Count > 0)
            {
                KQ = (int)dt.Rows[0][0];
            }
            return KQ;
        }


        /// <summary>
        /// Tim Mat Hang
        /// </summary>
        ///
        public DataTable TimKiem(string TuKhoa, int LoaiMatHang)
        {
            DataTable dt = new DataTable();
            string strQuery = "";
            strQuery = String.Format("SELECT MaMatHang,TenMatHang,TenLoaiMatHang,TenDonVi,SoLuongTon,GiaThamKhao,GhiChu,CASE TrangThai WHEN 1 THEN N'Bật' ELSE N'Tắt' END 'TrangThai',MH.NgayLap FROM MATHANG MH,LOAIMATHANG L, DONVITINH D WHERE MH.LoaiMatHang = L.MaLoaiMatHang AND MH.DonViTinh = D.MaDonVi AND TenMatHang like N'%{0}%'", TuKhoa);
            if (LoaiMatHang != 0)
            {
                strQuery += String.Format(" AND MH.LoaiMatHang = {0}", LoaiMatHang);
            }
            dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// Tim Mat Hang
        /// </summary>
        ///
        public DataTable TimKiemMatHangDeBan(string TuKhoa, int LoaiMatHang)
        {
            DataTable dt = new DataTable();
            string strQuery = "";
            strQuery = String.Format("SELECT MaMatHang,TenMatHang,TenLoaiMatHang,TenDonVi,SoLuongTon,GiaThamKhao,GhiChu FROM MATHANG MH,LOAIMATHANG L, DONVITINH D WHERE MH.LoaiMatHang = L.MaLoaiMatHang AND MH.DonViTinh = D.MaDonVi AND TrangThai = 1 AND TenMatHang like N'%{0}%'", TuKhoa);
            if (LoaiMatHang != 0)
            {
                strQuery += String.Format(" AND MH.LoaiMatHang = {0}", LoaiMatHang);
            }
            dt = KetNoiCSDLDAO.ExecuteQuery(strQuery);
            return dt;
        }

        /// <summary>
        /// Xoa Tat ca Mat Hang
        /// </summary>
        /// 
        public bool XoaTatCaMatHang()
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("DELETE FROM MATHANG");
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        /// <summary>
        /// Cap nhat so luong ton sau khi thanh toan hoa don
        /// </summary>
        ///
        public bool CapNhatSoLuongTon(string MaMH, int SoLuongTon)
        {
            bool KQ = false;
            string strQuery = "";
            strQuery = String.Format("UPDATE MATHANG SET SoLuongTon = {0} WHERE MaMatHang = '{1}'", SoLuongTon, MaMH);
            KQ = KetNoiCSDLDAO.ExecuteNonQuery(strQuery);
            return KQ;
        }

        //Kha Nguyen - Mat hang DAO - 6/5/2013 - end 


        #region Quang Dat Them Bao Cao Mat Hang 14/06/2013

        public static DataTable Load_BaoCao_MatHang()
        {
            string sql = "";
            sql += " Select mh.TenMatHang,lmh.TenLoaiMatHang,mh.SoLuongTon";
            sql += " from MatHang mh,LoaiMatHang lmh";
            sql += " where mh.LoaiMatHang=lmh.MaLoaiMatHang";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }

        #endregion

    }
}
